package com.library.dao.impl;

import com.library.dao.BookReviewDao;
import com.library.entity.BookReview;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;
import javax.sql.DataSource;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

@Repository
public class BookReviewDaoImpl implements BookReviewDao {
    @Autowired
    private DataSource dataSource;

    @Override
    public int addReview(BookReview review) {
        String sql = "INSERT INTO book_reviews (book_id, reader_id, rating, review_text) VALUES (?, ?, ?, ?)";
        try (Connection conn = dataSource.getConnection();
                PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setLong(1, review.getBookId());
            stmt.setLong(2, review.getReaderId());
            stmt.setInt(3, review.getRating());
            stmt.setString(4, review.getReviewText());
            return stmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
            return 0;
        }
    }

    @Override
    public List<BookReview> getBookReviews(Long bookId) {
        List<BookReview> reviews = new ArrayList<>();
        String sql = "SELECT r.*, ri.name as reader_name FROM book_reviews r " +
                "JOIN reader_info ri ON r.reader_id = ri.reader_id " +
                "WHERE r.book_id = ? ORDER BY r.review_date DESC";
        try (Connection conn = dataSource.getConnection();
                PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setLong(1, bookId);
            ResultSet rs = stmt.executeQuery();
            while (rs.next()) {
                BookReview review = new BookReview();
                review.setReviewId(rs.getLong("review_id"));
                review.setBookId(rs.getLong("book_id"));
                review.setReaderId(rs.getLong("reader_id"));
                review.setRating(rs.getInt("rating"));
                review.setReviewText(rs.getString("review_text"));
                review.setReviewDate(rs.getTimestamp("review_date"));
                review.setReaderName(rs.getString("reader_name"));
                reviews.add(review);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return reviews;
    }

    @Override
    public double getAverageRating(Long bookId) {
        String sql = "SELECT AVG(rating) as avg_rating FROM book_reviews WHERE book_id = ?";
        try (Connection conn = dataSource.getConnection();
                PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setLong(1, bookId);
            ResultSet rs = stmt.executeQuery();
            if (rs.next()) {
                return rs.getDouble("avg_rating");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0.0;
    }

    @Override
    public int deleteReview(Long reviewId) {
        String sql = "DELETE FROM book_reviews WHERE review_id = ?";
        try (Connection conn = dataSource.getConnection();
                PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setLong(1, reviewId);
            return stmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
            return 0;
        }
    }

    @Override
    public boolean hasUserReviewed(Long bookId, Long readerId) {
        String sql = "SELECT COUNT(*) as count FROM book_reviews WHERE book_id = ? AND reader_id = ?";
        try (Connection conn = dataSource.getConnection();
                PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setLong(1, bookId);
            stmt.setLong(2, readerId);
            ResultSet rs = stmt.executeQuery();
            if (rs.next()) {
                return rs.getInt("count") > 0;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return false;
    }
}